package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.db.DBConn;
import dao.NoticeMagDao;
import domain.Notice;

/**
 * 公告管理Dao实现
 * 
 * May 27, 2009
 *
 * @author Fabian
 *
 */
public class NoticeMagImpl implements NoticeMagDao {

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#addNotice(vo.Notice)
	 */
	
	public boolean addNotice(Notice n) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "insert into t_notice values (null, ?, now(), ?, ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setString(1, n.getContent());
			pstmt.setDate(2, new java.sql.Date(n.getEnd_time().getTime()));
			pstmt.setInt(3, n.getAuth_id());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#delNotice(int)
	 */
	
	public boolean delNotice(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "delete from t_notice where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#getPageNotice(int, int)
	 */
	
	public List<Notice> getPageNotice(int start, int limit) {
		List<Notice> list = new ArrayList<Notice>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select n.*, t.name from t_notice n , t_teacher t where n.auth_id = t.id order by n.pub_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Notice n = new Notice();
				n.setId(rs.getInt("id"));
				n.setContent(rs.getString("content"));
				n.setAuthor(rs.getString("name"));
				n.setPub_time(rs.getTimestamp("pub_time"));
				//判断是否过期
				Date now = new Date();
				Date end_time = null;
				end_time = rs.getTimestamp("end_time");
				if (now.getTime() > end_time.getTime()) {
					n.setStatus("已过期");
				} else {
					n.setStatus("正在公布中");
				}
				n.setEnd_time(end_time);
				
				list.add(n);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#getTotalCount()
	 */
	
	public int getTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_notice";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#modifyNotice(vo.Notice)
	 */
	
	public boolean modifyNotice(Notice n) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_notice set end_time=?, content=? where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setDate(1, new java.sql.Date(n.getEnd_time().getTime()));
			pstmt.setString(2, n.getContent());
			pstmt.setInt(3, n.getId());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.NoticeMagDao#displayNotice()
	 */
	
	public List<Notice> displayNotice() {
		List<Notice> list = new ArrayList<Notice>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select n.*, t.name from t_notice n , t_teacher t where n.auth_id = t.id and n.end_time > now() order by n.pub_time desc";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Notice n = new Notice();
				n.setId(rs.getInt("id"));
				n.setContent(rs.getString("content"));
				n.setAuthor(rs.getString("name"));
				n.setPub_time(rs.getTimestamp("pub_time"));
				n.setEnd_time(rs.getTimestamp("end_time"));				
				list.add(n);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

}
